package com.demo.springBootDemo06.service.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import com.demo.springBootDemo06.entity.Dept;
import com.demo.springBootDemo06.service.DeptService;

@Service
public class DeptServiceImpl implements DeptService {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public List<Dept> findList() {
		String sql = "select deptno,dname,loc from dept where 1=1";
		List<Dept> list = jdbcTemplate.query(sql, new RowMapper<Dept>() {
			@Override
			public Dept mapRow(ResultSet rs, int arg1) throws SQLException {
				Dept dept = new Dept();
				dept.setDeptno(rs.getInt("DEPTNO"));
				dept.setDname(rs.getString("DNAME"));
				dept.setLoc(rs.getString("LOC"));
				return dept;
			}
		});
		return list;
	}

	@Override
	public Dept getByPrimaryKey(Integer deptno) {
		String sql = "select deptno,dname,loc from dept where deptno=?";
		List<Dept> list = jdbcTemplate.query(sql, new Object[] { deptno }, new RowMapper<Dept>() {
			@Override
			public Dept mapRow(ResultSet rs, int arg1) throws SQLException {
				Dept dept = new Dept();
				dept.setDeptno(rs.getInt("DEPTNO"));
				dept.setDname(rs.getString("DNAME"));
				dept.setLoc(rs.getString("LOC"));
				return dept;
			}
		});
		Assert.isTrue(list.size() == 1);
		return list.get(0);
	}

	@Override
	public Dept addDept(final Dept dept) {
		String sql = "insert into dept(deptno,dname,loc) values (?,?,?)";
		jdbcTemplate.execute(sql, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setInt(1, dept.getDeptno());
				ps.setString(2, dept.getDname());
				ps.setString(3, dept.getLoc());
				return ps.executeUpdate();
			}
		});
		return dept;
	}

	@Override
	public Dept updateDept(final Integer deptno, final Dept dept) {
		Dept deptOrg = getByPrimaryKey(deptno);
		Assert.isTrue(deptOrg != null);
		String sql = "update dept set dname=?,loc=? where deptno=?";
		jdbcTemplate.execute(sql, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setString(1, dept.getDname());
				ps.setString(2, dept.getLoc());
				ps.setInt(3, deptno);
				return ps.executeUpdate();
			}
		});
		return deptOrg;
	}

	@Override
	public Dept deleteByPrimaryKey(final Integer deptno) {
		Dept deptOrg = getByPrimaryKey(deptno);
		Assert.isTrue(deptOrg != null);
		String sql = "delete from dept where deptno=?";
		jdbcTemplate.execute(sql, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setInt(1, deptno);
				return ps.executeUpdate();
			}
		});
		return deptOrg;
	}

}
